<?php
session_start();
include 'inc/db_connect.php';
include 'inc/db_op.php';
include 'inc/html_op.php';
include 'inc/form_op.php';
date_default_timezone_set('Asia/Chongqing');

$page_title = "Report";
$debug = 0;

$today = strtotime("today");

function get_trip_out_persons($start_day, $end_day) {
  $sql = "select count(distinct user_id) from work_log join task_info on work_log.task_id = task_info.id ".
         "  where is_trip_out = 1 and end_time between '".to_mysql_date($start_day)."' and '".to_mysql_date($end_day)."'".
		 " and work_log.user_id in (select id from user_info where department_id = ".$_SESSION['department_id'].")";
  return db_get_one_value($sql);
}

function get_project_summary($begin_day, $end_day) {
  $html_str = "";

  $view_department_id = get_view_department_id();
  
  $html_str .= '<h2>'.get_department_name_disp($view_department_id).'阶段项目进展一览<br/>（'.date("Y.n.j", $begin_day).'-'.date("Y.n.j", $end_day).'）</h2>';
  $between_str = " between '".to_mysql_date($begin_day)."' and '".to_mysql_date($end_day)."' ";
  $sql = "select project_info.project_name project, group_concat(work_log.title) work ".
         " from work_log join task_info join project_info ".
		 " on task_info.project_id = project_info.id and work_log.task_id = task_info.id ".
		 " where user_id in (select id from user_info where department_id = ".$view_department_id.") ".
		 " and end_time $between_str ".
		 " group by task_info.project_id ";
  $rows = db_get($sql);
  $fields = array("project"=>"项目", "work"=>"主要工作");
  $html_str .= gen_table($fields, $rows);
  return $html_str;
}

function get_trip_out_persondays($start_day, $end_day) {
  $sql = "select sum(trip_days) from ".
         "(select count(distinct to_days(end_time)) trip_days ".
		 " from work_log join task_info on work_log.task_id = task_info.id where is_trip_out = 1 and ".
		 " and work_log.user_id in (select id from user_info where department_id = ".$_SESSION['department_id'].") and ".
		 " end_time between '".to_mysql_date($start_day)."' and '".to_mysql_date($end_day)."' group by user_id) x";
  return db_get_one_value($sql);
}

function get_view_department_id() {
  $view_department_id = $_SESSION['department_id'];
  
  if(is_senior_manager($_SESSION['user_id']) && !is_manager($_SESSION['user_id'])) {
    if(isset($_SESSION['view_department_id'])) {
      $view_department_id = $_SESSION['view_department_id'];
    } else {
      $sql = "select department.id department_id from department join user_info ".
             " on department.id = user_info.department_id ".
             " join work_log on user_info.id = work_log.user_id ".
             " where work_log.id in (select max(id) from work_log) ";
      // echo 'sql: '.$sql;
      $rows = db_get($sql);
      if(count($rows) > 0) {
        $view_department_id = $rows[0]['department_id'];
      }
    }
  }
  
  // echo ' view_department_id: '.$view_department_id;
  
  return $view_department_id;
}

function get_department_name_disp($department_id, $gen_list = 0) {
  $html_str = "";

  if((1 == $gen_list) && is_senior_manager($_SESSION['user_id']) && !is_manager($_SESSION['user_id'])) {
    $sql = "select id, name from department where id = $department_id or inactive = 0 order by name";
    // echo 'sql: '.$sql;
    $rows = db_get($sql);
    foreach($rows as $row) {
      $options[$row['id']] = $row['name'];
    }
    $html_str .= gen_select('view_department_id', $options, $department_id);
  } else {
    $sql = "select name from department where id = $department_id";
    $html_str .= db_get_one_value($sql);
  }

  return $html_str;  
}

function get_last_week_summary($begin_day, $end_day) {
  $html_str = "";
  
  $view_department_id = get_view_department_id();

  $html_str .= '<form method=post>';
  
  $html_str .= '<h2>'.get_department_name_disp($view_department_id, 1).'阶段工作详细情况<br/>';
  $begin_day_str = date("Y-m-d",$begin_day);
  $end_day_str = date("Y-m-d",$end_day);
  $input_begin_day = "<input type=date name=begin_day value=$begin_day_str />";
  $input_end_day = "<input type=date name=end_day value=$end_day_str />";
  $html_str .= $input_begin_day.'-'.$input_end_day.'<button type=submit>重新生成</button> </h2>';
  $html_str .= '</form>';

  $between_str = " between '".to_mysql_date($begin_day)."' and '".to_mysql_date($end_day)."' ";
  $sql = 'select t.id tid, t.title title, t.progress progress, u.id uid, u.name uname, u.display_order, u.department_id from task_info t join user_info u on t.assignto = u.id where t.id in '.
         "(select task_id from work_log where end_time $between_str)".
		 " and u.department_id = ".$view_department_id." order by u.display_order";
  
  // echo 'sql: '.$sql;
  
  $tasks = db_get($sql);
  $seq = 1;
  $year = date("Y", $end_day);
  $month = date("m", $end_day);
  $week = strftime("%V",$end_day);
  if ( $month == 1 && $week > 5) {
      $year -= 1;
  }
  $html_str .= "<table border=1 width=90%><tr><th width=4%>序号</th><th>工作内容</th><th width=7%>工作量<br/>（天）</th><th width=6%>承担人</th><th width=7%>完成情况</th><th onmouseover=show_div('all_rele_plan_div_id'); onmouseout=hide_div('all_rele_plan_div_id'); width=30%>备注<div id='all_rele_plan_div_id' style='display:none'><button onclick=switch_all_rele_plan_div_disp();>显示/隐藏所有计划及批注</button></div></th><th width=25%>备注2</th><tr>";
  foreach($tasks as $task) {
    $from_where_str = " from work_log where task_id = ".$task['tid']." and end_time $between_str";
    $hours_sql = "select sum(work_hours) $from_where_str";
	//echo 'hours_sql:'.$hours_sql;
	//echo ' wlog_title_sql:'.$wlog_title_sql;
    $html_str .= "<tr>";
	$html_str .= "<td align='center'>$seq</td>";
	$html_str .= "<td>".$task['title']."</td>";
	$html_str .= "<td align='center'>".round(db_get_one_value($hours_sql)/8, 1)."</td>";
	$html_str .= "<td align='center'>".$task['uname']."</td>";
    $html_str .= "<td align='center'>".($task['progress']>=100?'完成':'继续')."</td>";

    $outline_sql = 'select id, plan, plan_comment, outline, outline_comment from work_outline where year = '.$year.' and week = '.$week.' and task_id = '.$task['tid'].' order by year, week desc';
    $outline_content = db_get($outline_sql);
    if (count($outline_content) == 0) {
        $outline_content['plan'] = '';
        $outline_content['plan_comment'] = '';
        $outline_content['outline'] = '';
        $outline_content['outline_comment'] = '';
    } else {
        $outline_content = $outline_content[0];
    }

	$wlog_sql = "select title, date_format(end_time, '%c-%d %a') end_time, overtime_hours, content $from_where_str order by end_time";
    // echo 'wlog_sql: '.$wlog_sql;
	$logs = db_get($wlog_sql);
    $title_contents = array();

	foreach($logs as $log) {
	  $overtime_str = '';
	  $overtime_hours = $log['overtime_hours'];
	  if($overtime_hours > 0.5) {
	    $overtime_str .= '，<b>加班</b>'.$overtime_hours.'小时';
	  }
      array_push($title_contents, $log['end_time'].'，'.$log['title'].$overtime_str.'<br/>'.str_replace(chr(13), '<br/>', $log['content']));
	}
    $html_str .= gen_outline_td($year, $week, $task['tid'], $outline_content, $task['uid']);
	$html_str .= "<td>".gen_list($title_contents,'ul')."</td>";

	$html_str .= "</tr>";
	++$seq;
  }
  
  $html_str .= "</table>";
  
  // $html_str .= "<p>累计出差".get_trip_out_persons($begin_day, $end_day).
  //             "人，累计".get_trip_out_persondays($begin_day, $end_day)."人天。</p>";

  return $html_str;
}

function get_future_work($start_before) {  
  $html_str = "";
  
  $view_department_id = get_view_department_id();

  $html_str .= '<h2>'.get_department_name_disp($view_department_id).'后续工作安排详细情况<br/>（'. date("Y.n.j", $GLOBALS['today']) .'）</h2>';
  $html_str .= "<table border=1 width=90%><tr><th width=4%>序号</th><th>工作内容</th><th width=8%>预计工作量<br>（天）</th><th width=6%>承担人</th><th width=60%>备注</th></tr>";
  
  $sql = "select t.id tid, t.title title, t.content content, t.progress progress, u.id uid, u.name uname, u.display_order, u.department_id ".
         " from task_info t join user_info u on t.assignto = u.id ".
		 " where u.department_id = ".$view_department_id." and progress < 100 ".
		 " and scheduled_start_time  <= '".to_mysql_date($start_before)."' ".
		 " order by u.display_order";
  //echo 'sql :'.$sql;
  $tasks = db_get($sql);
  
  $seq = 1;
  $year = date("Y", $start_before);
  $month = date("m", $start_before);
  $week = strftime("%V",$start_before);
  if ( $month == 1 && $week > 5) {
      $year -= 1;
  }
  foreach($tasks as $task) {
    $estimated_work_hours = '';
	if($task['progress'] > 0) {
	  $sql = 'select sum(work_hours) from work_log where task_id = '.$task['tid'];
	  $work_hours = db_get_one_value($sql);
	  if($work_hours > 0) {
	    $estimated_work_hours = (100 - $task['progress']) * $work_hours / $task['progress'];
	  }
	}
    
    $plan_sql = 'select id, plan, plan_comment from work_outline where year = '.$year.' and week = '.$week.' and task_id = '.$task['tid'].' order by year, week desc';
    $plan_content = db_get($plan_sql);
    if (count($plan_content) == 0) {
        $plan_content['plan'] = '';
        $plan_content['plan_comment'] = '';
    } else {
        $plan_content = $plan_content[0];
    }
    
    $html_str .= "<tr><td align=center>$seq</td><td>".$task['title']."</td><td align=center>".round($estimated_work_hours/8, 1)."</td><td align=center>".$task['uname']."</td>".gen_plan_td($year, $week, $task['tid'], $plan_content, $task['uid']);
	
	$html_str .= "</tr>";
	++$seq;
  }
  //echo $sql;
  
  $html_str .= "</table>";
  return $html_str;
}

function get_common_where_str($start_time, $end_time) {
  $where_str = " end_time between '".to_mysql_date($start_time)."' and '".to_mysql_date($end_time)."' ".
		 " and user_id = ".$_SESSION['user_id']; 
  return $where_str;
}

function get_total_workhour($start_time, $end_time) {
  $sql = "select sum(work_hours) from work_log where ".get_common_where_str($start_time, $end_time);
  // echo $sql;
  return db_get_one_value($sql);
}

function get_onetype_workhour($start_time, $end_time, $type_id) {
  $sql = "select sum(work_hours) from work_log join task_info on work_log.task_id = task_info.id ".
         " where type_id = $type_id and ".get_common_where_str($start_time, $end_time);
  // echo $sql;
  return db_get_one_value($sql);
}

function get_report_one_row($start_time, $end_time, $period_name, $types) {
  $html_str = "";
  $total_workhour = get_total_workhour($start_time, $end_time);
  if($total_workhour < 0.1){
    return $html_str;
  }
  $html_str .= "<tr><td align=center>$period_name</td><td align=right>".$total_workhour."</td>";
  foreach($types as $type) {
    $onetype_workhour = get_onetype_workhour($start_time, $end_time, $type['type_id']);
	$html_str .= "<td align=right>".round($onetype_workhour,1)."</td><td align=right><b>".round(100.*$onetype_workhour/$total_workhour)."%</b></td>";
  }
  $html_str .= "</tr>";
  return $html_str;
}

function get_report_table() {
  $html_str = "";
  $sql = "select distinct task_type.id type_id, task_type.name type_name ".
         "from work_log join task_info on work_log.task_id = task_info.id join task_type on type_id = task_type.id ".
		 "where user_id = ".$_SESSION['user_id']." order by type_id";
  // echo $sql;
  $types = db_get($sql);
  $html_str .= "<table border=1>";

  //table header
  $html_str .= "<tr><th rowspan=2>时段</th><th rowspan=2>工作小时</th>";
  foreach($types as $type) {
    $html_str .= "<th colspan=2 align=center>".$type['type_name']."</th>";
  }
  $html_str .= "</tr><tr>";
  foreach($types as $type) {
    $html_str .= "<th>工作小时</th><th>百分比</th>";
  }
  $html_str .= "</tr>";
  
  $today = strtotime("today");
  
  //this week
  $tomorrow = strtotime("tomorrow");
  $monday = strtotime("this monday");
  $html_str .= get_report_one_row($monday, $tomorrow, "本周", $types);

  //monthly
  $min_time = strtotime(db_get_one_value("select min(end_time) from work_log where user_id = ".$_SESSION['user_id']));
  $first_month = strtotime(date('Y-m',$min_time) . '-01 00:00:00');
  $begin_time = strtotime(date('Y-m',$today) . '-01 00:00:00');
  while($begin_time > $first_month) {
    $month_str = date("Y.m", $begin_time);
    $html_str .= get_report_one_row($begin_time, strtotime("+1 month", $begin_time), $month_str, $types);
	$begin_time = strtotime("-1 month", $begin_time);
  }
  
  //all
  $html_str .= get_report_one_row(0, $tomorrow, "全部", $types);
  
  $html_str .= "</table>";
  return $html_str;
}

function get_work_hour_table() {
  $sql = 'SELECT name, format(SUM( work_hours ), 2) wh '.
         'FROM work_log '.
         'JOIN user_info ON work_log.user_id = user_info.id where user_info.disabled = 0 or user_info.disabled is null '.
		 'GROUP BY user_id HAVING wh > 80 '.
		 'ORDER BY sum(work_hours) DESC ';
  $rows = db_get($sql);
  $fields = array("name"=>"姓名", "wh"=>"工作小时数");
  return gen_table($fields, $rows);
}

if(isset($_POST['view_department_id'])) {
  $_SESSION['view_department_id'] = $_POST['view_department_id'];
}
if(isset($_POST['end_day'])) {
  $end_day = strtotime($_POST['end_day']);
} else {
  $end_day = $today;
}
if(isset($_POST['begin_day'])) {
  $begin_day = strtotime($_POST['begin_day']);
} else {
  $begin_day = strtotime('last Monday', strtotime('this Sunday', $end_day));
}

$_SESSION['is_manager'] = is_manager($_SESSION['user_id']);

$content = get_last_week_summary($begin_day, $end_day);

if(!is_senior_manager($_SESSION['user_id']) || is_manager($_SESSION['user_id'])) {
  $content .= get_project_summary($begin_day, $end_day);
}

$content .= get_future_work(strtotime("+1 week", strtotime('this Sunday')));

if(!is_senior_manager($_SESSION['user_id']) || is_manager($_SESSION['user_id'])) {
  $content .= "<h2>个人工作小时汇总和分类统计</h2>".get_report_table();
}

//$content .= "<h2>工作小时汇总统计</h2><p>(小于80小时的暂未显示)</p>".get_work_hour_table();

echo gen_onecol_page($page_title, $content, $debug);

?>
